package myBean;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Huong
 */
public class Project {
    private String code;
    private String name;
    private String status;
    private String sDate;
    private String eDate;
    private int employee;

    public Project() {
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String geteDate() {
        return eDate;
    }

    public void seteDate(String eDate) {
        this.eDate = eDate;
    }

    public int getEmployee() {
        return employee;
    }

    public void setEmployee(int employee) {
        this.employee = employee;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getsDate() {
        return sDate;
    }

    public void setsDate(String sDate) {
        this.sDate = sDate;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
    public String getProjectTable() {
        String kq = "";
        try {
             String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=CategoryDB;user=sa;password=sa";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            String SQL = "SELECT * FROM project";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);

            while (rs.next()) {
                kq += "<tr>";
                kq += "<td>" + rs.getString(code);
                kq += "<td>" + rs.getString(name);
                kq += "<td>" + rs.getString(status);
                kq += "<td>" + rs.getString(sDate);
                kq += "<td>" + rs.getString(eDate);
                kq += "<td>" + rs.getInt(employee);
                kq += "<td><a href=\"Edit.jsp?x="+rs.getString("Name")+"\">Edit</a>\n"
                            + "|" + "<a href=\"Delete.jsp?x=" + rs.getString("Name") +"\">Delete</a></td>";
                kq += "</tr>";
            }
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }
    
    public void delete(String name){
        try {
             String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=CategoryDB;user=sa;password=sa";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            String deleteQ = "delete from puch where name = '" + name + "'";
            PreparedStatement deleteSt = con.prepareStatement(deleteQ);
            deleteSt.execute();

        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void creat(String pcode,String pname, String status, String sDate, String eDate, int emp){
        try {
             String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=CategoryDB;user=sa;password=sa";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            String insertQ = "insert into Puch values(?,?,?,?,?,?)";
                    PreparedStatement insertSt = con.prepareStatement(insertQ);
                    insertSt.setString(1, pcode);
                    insertSt.setString(2, pname);
                    insertSt.setString(3, status);
                    insertSt.setDate(4, Date.valueOf(sDate));
                    insertSt.setDate(5, Date.valueOf(eDate));
                    insertSt.setInt(6, emp);
                    insertSt.execute();

        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public boolean edit(String pcode,String pname, String status, String sDate, String eDate, int emp){
        try {
             String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=CategoryDB;user=sa;password=sa";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

           String insertQ = "UPDATE Puch SET Status='" + status + "', Code='" + pcode + "', Name='" + pname + "',[Start Date] = '"+ Date.valueOf(sDate) +"',[End Date] = '"+ Date.valueOf(eDate) +"', Employee = "+ emp +" WHERE Code='" + pcode + "'";
                PreparedStatement insertSt = con.prepareStatement(insertQ);
                insertSt.execute();
                return true;
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
}
